#!/use/bin/python
# coding=utf-8
# 将 开发指标-每日开机步骤 输出到飞书
import redis
import datetime
import pymysql
import requests
import json
import pandas as pd
from tabulate import tabulate

from dbutils.pooled_db import PooledDB


logDetail = []

# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf', port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.150.109', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',port=3306)  # 1为连接池里的最少连接数
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

#根据传入的SQL 返回执行SQL返回的数量
def selectNumBySql(sql,conf=2):
    if conf == 1:
        conn, cur = getConfConnection()
    else:
        conn, cur = getDataConnection()
    #print(sql)
    cur.execute(sql)
    numResult = cur.fetchone()
    num = 0
    if numResult is not None:
        num = numResult[0]
    return num

#计算百分比 保留两位小数  如:34.88%
# X为分子 Y为分母
def getRateByXY(X,Y):
    rate = 0
    if(X is None or Y is None):
        return rate
    if Y != 0:
        if X > Y:
            rate = 100
        else:
            round(X * 100/ Y, 2)
    rate = f"{rate}%"
    return rate

# redis
def getReidsConnection():
    redis_info = {
        "host": "172.20.151.90",
        "password": "Coocaa2022",
        "port": 6379,
        "db": 8
    }

    r = redis.Redis(**redis_info, decode_responses=True)
    return r

# 计算 开发指标-每日开机步骤
#date 当天
def saveDeviceOn():
    try:
        #① 创建提醒定时任务成功率
        #获取分母:早间服务订阅设备量(昨日)
        subNum = selectNumBySql(
            f"""
            select count(1) from proactive_service_devices t where t.active_id in (
                select tt.active_id from life_assistant_devices tt where  (tt.switches_state1 & (1 << (1 - 1)))<>0 and 	
                (tt.switches_state1 & (1 << (2 - 1)))<>0 and 	(tt.switches_state1 & (1 << (18 - 1)))<>0) and t.wakeup_time is not null """,1)
        #获取分子:创建提醒定时任务成功的设备量(昨日)
        createTaskNum = selectNumBySql(
            f"select  count(DISTINCT(t.active_id))  from log_common_service_{yesterdayYYMMdd} t where t.`key` = 'createTaskResult'  and JSON_EXTRACT(t.msg,'$.result') ='1' ")
        #创建提醒定时任务成功率
        createTaskRate = getRateByXY(createTaskNum,subNum)
        logDetail.append(f"1.创建提醒定时任务成功率为{createTaskRate},分母:早间服务订阅设备量(昨日)为{subNum},分子:创建提醒定时任务成功的设备量(昨日)为{createTaskNum}\n\n")
        # ② 缓存任务执行成功率
        #获取分母:准备执行缓存任务的设备量
        cachePreStartNum = selectNumBySql(
            f" select count(DISTINCT(t.active_id)) '设备数' from log_common_service_{todayYYMMdd} t where t.`key` = 'cachePreStart' ")
        #获取分子:缓存任务执行成功的设备量
        cacheSuccessNum = selectNumBySql(
            f""" select  count(DISTINCT(t.active_id))  from log_common_service_{todayYYMMdd} t where t.`key` = 'cacheResult'
         and JSON_EXTRACT(t.msg,'$.result') =1 """)
        cacheFailNum = selectNumBySql(
            f""" select  count(DISTINCT(t.active_id))  from log_common_service_{todayYYMMdd} t where t.`key` = 'cacheResult'
                 and JSON_EXTRACT(t.msg,'$.result') = 0 """)
        #缓存任务执行成功率
        cacheSuccessRate = getRateByXY(cacheSuccessNum,cachePreStartNum)
        cacheFailRate = getRateByXY(cacheFailNum, cachePreStartNum)
        logDetail.append(
            f"2.缓存任务执行失败率为{cacheFailRate},分母:准备执行缓存任务的设备量为{cachePreStartNum},分子:缓存任务执行失败的设备量为{cacheFailNum}\n\n")
        # ③ 早间播报启动率
        #获取分母:创建提醒定时任务成功的设备量 （昨天）
        #createTaskNum
        #获取分子:早间播报准备启动 （今天)
        tipTaskPreStartNum = selectNumBySql(
            f""" select  count(DISTINCT(t.active_id))  from log_common_service_{todayYYMMdd} t where t.`key` = 'tipTaskPreStart' """)
        #早间播报启动率
        tipTaskPreStartRate = getRateByXY(tipTaskPreStartNum,createTaskNum)
        logDetail.append(
            f"3.早间播报启动率为{tipTaskPreStartRate},分母:创建提醒定时任务成功的设备量为{createTaskNum},分子:早间播报准备启动设备量为{tipTaskPreStartNum}\n\n")
        # ④ 早间播报启动执行成功率
        # 获取分母：早间播报准备启动
        # tipTaskPreStartNum
        # 获取分子:早间播报启动成功
        tipTaskStartNum = selectNumBySql(
            f""" select count(DISTINCT(t.active_id)) from log_common_service_{todayYYMMdd} t where t.`key` = 'openScreen'  """)
        # 早间播报启动执行成功率
        tipTaskStartRate = getRateByXY(tipTaskStartNum, tipTaskPreStartNum)
        logDetail.append(
            f"4.早间播报启动执行成功率为{tipTaskStartRate},分母:早间播报准备启动设备量为{tipTaskPreStartNum},分子:早间播报启动成功设备量为{tipTaskStartNum}\n\n")
        # ⑤ 音乐播放成功率
        # 获取分母：启动音乐页面成功设备量
        musicPageStartNum = selectNumBySql(
            f""" select count(DISTINCT(t.active_id))  from log_common_service_{todayYYMMdd} t where t.`key` = 'musicPageStart'   """)
        # 获取分子:音乐播放成功
        musicStartPlayNum = selectNumBySql(
            f""" select  count(DISTINCT(t.active_id)) from log_common_service_{todayYYMMdd} t where t.`key` = 'musicStartPlay' 
             and JSON_EXTRACT(t.msg,'$.result') = '1'  """)
        # 音乐播放成功率
        musicPlaySuccessRate = getRateByXY(musicStartPlayNum, musicPageStartNum)
        logDetail.append(
            f"5.音乐播放成功率成功率为{musicPlaySuccessRate},分母:启动音乐页面成功设备量为{musicPageStartNum},分子:音乐播放成功设备量为{musicStartPlayNum}\n\n")
        # ⑥ 音乐缓存失败率
        # 获取分母：音乐播放成功
        # musicStartPlayNum
        # 获取分子: 音乐缓存失败设备量
        musicCacheFailNum = selectNumBySql(
            f""" select count(DISTINCT(t.active_id)) from log_common_service_{todayYYMMdd} t where t.key ='musicPageFin' 
            and JSON_EXTRACT(t.msg,'$.result') = 0 and  JSON_EXTRACT(t.msg,'$.reason') = 'music cache failed'  """)
        # 音乐缓存失败率
        musicCacheFailRate = getRateByXY(musicCacheFailNum, musicStartPlayNum)
        logDetail.append(
            f"6.音乐缓存失败率为{musicCacheFailRate},分母:启动音乐页面成功设备量为{musicStartPlayNum},分子:音乐缓存失败设备量为{musicCacheFailNum}\n\n")
        # ⑦ 天气页面启动成功率
        # 获取分母：天气页面准备启动
        weatherPagePreStartNum = selectNumBySql(
            f""" select count(DISTINCT(t.active_id))  from log_common_service_{todayYYMMdd} t where t.`key` = 'weatherPagePreStart'   """)
        # 获取分子: 天气页面启动成功
        weatherPageStartNum = selectNumBySql(
            f""" select count(DISTINCT(t.active_id))  from log_common_service_{todayYYMMdd} t where t.`key` = 'weatherPageStart' 
                    and  JSON_EXTRACT(t.msg,'$.result') = 1 and  JSON_EXTRACT(t.msg,'$.isFromMusic') =1  """)
        # 天气页面启动成功率
        weatherPageStartRate = getRateByXY(weatherPageStartNum, weatherPagePreStartNum)
        logDetail.append(
            f"7.天气页面启动成功率为{weatherPageStartRate},分母:天气页面准备启动设备量为{weatherPagePreStartNum},分子:天气页面启动成功设备量为{weatherPageStartNum}\n\n")
        #⑧ 早间服务异常分布
        #⑨ 早间服务异常次数
        abnormalNum = selectNumBySql(
            f""" select count(DISTINCT(t.active_id))   from log_common_service_{todayYYMMdd} t where t.`key` = 'intoUnusualPage' """)
        #⑩ 早间服务异常占比
        abnormalRate = getRateByXY(abnormalNum, tipTaskStartNum)
        logDetail.append(
            f"8.早间服务异常占比为{abnormalRate},分母:早间播报启动成功设备量为{tipTaskStartNum},分子:早间服务异常次数为{abnormalNum}\n\n")
        #⑪ 服务总体执行成功率
        # 获取分母：早间播报启动成功
        # tipTaskStartNum
        # 获取分子: 服务总体完成次数
        tipTaskFinishNum = selectNumBySql(
            f""" select count(DISTINCT(t.active_id)) from log_common_service_{todayYYMMdd} t where t.key ='tipTaskFinish' """)
        # 服务总体执行成功率
        tipTaskFinishRate = getRateByXY(tipTaskFinishNum, tipTaskStartNum)
        logDetail.append(
            f"9.服务总体执行成功率为{tipTaskFinishRate},分母:早间播报启动成功设备量为{tipTaskStartNum},分子:服务总体完成次数设备量为{tipTaskFinishNum}\n\n")
        print(logDetail)

    except Exception as e:
        print(e)
    finally:
        print('')


# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    return re_date

#如 2022年12月13号 获取 221213 作为分表后缀
def getdateyyMMdd(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y%m%d')
    re_date = re_date[2:]
    return re_date



def printFeiShu(msg):
    msg_content = "{env}\n\n{msg}".format(env=env, msg="".join(msg))
    print(msg_content)

    payload = {
        "msg_type": "text",
        "content": {
            "text": msg_content
        }
    }

    headers = {
        'Content-Type': 'application/json'
    }

    data = json.dumps(payload)

    # 按照utf-8编码成字节码
    data = data.encode("utf-8")
    #测试机器人
    url = 'https://open.feishu.cn/open-apis/bot/v2/hook/4238d051-3d9a-438f-9614-eb324f2ea845'
    #正式机器人
    #url = "https://open.feishu.cn/open-apis/bot/v2/hook/4b102c84-338a-4bef-87f9-441ecd3f737b"
    #response = requests.post(url, data=data, headers=headers)

if __name__ == '__main__':
    d =0
    #today = todayYMD()
    toDay = getdate(d)
    yesterdayYYMMdd = getdateyyMMdd(d+1)
    todayYYMMdd = getdateyyMMdd(d)
    env = f"{toDay}日期,开发指标-每日开机步骤"
    print(f"{toDay}日期,开发指标-每日开机步骤")
    saveDeviceOn()
    printFeiShu(logDetail)


